Median & Rank

You may be familiar with several of the statistical functions included in Excel 2000.  We will discuss some of these functions, including sum, average, median, rank, large and small.  

To explore these functions, open the file, Funexam from your data disk.

Notice the tabs at the bottom of the spreadsheet.  

There are tabs correspond with the different categories of functions we will be dealing with.

The most widely used function is the sum function.  This function can be accessed using the AutoSum button on the toolbar, or by writing the function equation.  You can view the sum of a column by selecting the cells you want to add, and than clicking on the sum icon.

Another important function is the average function.  To use this function, type =average and select the cells of which you would like to see the average.  

Now let’s take a look at the median function.  The median entry of a set of cells, is the number that would appear in the middle if all of the numbers were lined up numerically.  This applies to an odd numbered set.  If the set is an even numbered set, the middle two numbers are averaged to calculate the median.  

The average may come up as different from the median.  This can be helpful in certain cases.

Notice that column B in the Fun Exam file is titled Rank.  These entries are not sorted.  If the column had been previously sorted, the rank function may not be necessary.  However, in an unsorted column, rank becomes useful in recognizing the order of a particular number.

The rank function basically takes the value of one cell and compares it with the values of the other selected cells.  The rank function allows you to quickly see what in what order of a hierarchy a particular value would fall.  In the case of a duplicated cell value, Excel will rank both of those cells with the same rank.  However, the next rank will be skipped.  For instance, as demonstrated in the training tape, if two cells have a value of 86, both of those cells will receive the same ranking (in this case a ranking of 12).  However, the next value will not receive a ranking of 13, but will receive a ranking of 14.  

Skill Builder:  Ranking Data

If you are in doubt concerning the ranking of a cell value, you can sort the data to verify the ranking.

1.	Select	The Cells you want to sort.

2.	Click	On the A-Z or Z-A Sort Buttons.  The data in the 
		highlighted cells will be sorted and you can verify 
		the ranking.	

Keep in mind that the rank function will work whether or not the original data is sorted.  This is an excellent tool when working with databases in which it is inappropriate to sort data.  The rank function is also a dynamic function.  This means that if one number in the dataset is changed, the ranking will be automatically updated to reflect the change.